package com.isoft.mapper;

import com.isoft.entity.DfmeFrttEntity;
import org.apache.ibatis.annotations.*;

@Mapper
public interface DfmeFrttMapper {

    @Insert("INSERT INTO MSG_META(SENDER, RECEIVER, SEQUENCE_NUMBER, DATE_TIME, MSG_TYPE, SUB_TYPE) " +
            "VALUES(" +
            "#{meta.sender,jdbcType=VARCHAR}, " +
            "#{meta.receiver,jdbcType=VARCHAR}, " +
            "#{meta.sequenceNumber,jdbcType=NUMERIC}, " +
            "TO_TIMESTAMP(#{meta.dateTime,jdbcType=VARCHAR}, 'yyyymmddhh24miss'), " +
            "#{meta.type,jdbcType=VARCHAR}, " +
            "#{meta.subType,jdbcType=VARCHAR})")
    @Options(useGeneratedKeys = true, keyProperty = "msgId", keyColumn = "MSG_ID")
    void insertMeta(DfmeFrttEntity entity);

    @Insert("INSERT INTO FLIGHT_BASE(" +
            "FLIGHT_ID, FLIGHT_FILE_ID, FLIGHT_IDENTIFIER, STATUS) " +
            "VALUES(" +
            "#{dflt.flightId,jdbcType=NUMERIC}, " +
            "#{dflt.flightFileId,jdbcType=VARCHAR}, " +
            "#{dflt.flightIdentifier,jdbcType=VARCHAR}, " +
            "'NORMAL')")
    int insertFlightBase(DfmeFrttEntity entity);

    @Select("SELECT COUNT(1) FROM AIRPORT WHERE AIRPORT_CODE = #{airportCode}")
    int checkAirportExists(@Param("airportCode") String airportCode);

    @Insert("INSERT INTO AIRPORT(AIRPORT_CODE) VALUES(#{airportCode})")
    void insertAirport(@Param("airportCode") String airportCode);

    @Insert("MERGE INTO FLIGHT_ROUTE USING (SELECT #{flightId} AS FLIGHT_ID, #{airportCode} AS AIRPORT_CODE, #{airportSeq} AS AIRPORT_SEQ FROM DUAL) S ON (FLIGHT_ROUTE.FLIGHT_ID = S.FLIGHT_ID AND FLIGHT_ROUTE.AIRPORT_CODE = S.AIRPORT_CODE AND FLIGHT_ROUTE.AIRPORT_SEQ = S.AIRPORT_SEQ) WHEN NOT MATCHED THEN INSERT (FLIGHT_ID, AIRPORT_CODE, AIRPORT_SEQ, SCHEDULED_TIME, ESTIMATED_TIME) VALUES (#{flightId}, #{airportCode}, #{airportSeq}, TO_TIMESTAMP(#{scheduledTime}, 'yyyymmddhh24miss'), TO_TIMESTAMP(#{estimatedTime}, 'yyyymmddhh24miss'))")
    void insertFlightRoute(@Param("flightId") Long flightId,
                          @Param("airportCode") String airportCode,
                          @Param("airportSeq") Integer airportSeq,
                          @Param("scheduledTime") String scheduledTime,
                          @Param("estimatedTime") String estimatedTime);
}